﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SqlServerDriver
{
	public class CopyTableModel
	{
		public enum CopyObjectType {Table, View, Procedure} 
		// props must be public to be bound to controls
		// refactor/rename/delete these 
		public string SourceDb {get; set; }
		public string TargetDb {get; set; }
		public string SourceTable {get; set; }
		public string TargetTable {get; set; }
		public string OtherTables {get; set; }
		public CopyObjectType ObjectType {get; set; }
		public bool IsDataWanted {get; set; }
		public bool IsScriptWanted {get; set; }
		public string ScriptFileName {get; set; }
		public bool SpareBool {get; set; }
		public bool IsSchemaPreserved {get; set; }
		public bool IsCopyNameWanted {get; set; }

		public CopyTableModel()
		{
			this.SourceDb = "";
			this.TargetDb = "";
			this.SourceTable = "";
			this.TargetTable = "";
			this.OtherTables = "";
			this.ObjectType = CopyObjectType.Table;
			this.IsDataWanted = true;// NB: non-false default setting.
			this.IsScriptWanted = false;
			this.ScriptFileName = "";
			this.IsSchemaPreserved = false;
			this.SpareBool = true;  
		}

		// > Boilerplate: CopyFrom to the controller can make a working
		// copy from what it was sent in the constructor.
		// Equal method is for checking for changes.
		internal void CopyFrom(CopyTableModel source)
		{
			this.SourceDb = source.SourceDb;
			this.TargetDb = source.TargetDb;
			this.SourceTable = source.SourceTable;
			this.TargetTable = source.TargetTable;
			this.OtherTables = source.OtherTables;
			this.ObjectType = source.ObjectType;
			this.IsDataWanted = source.IsDataWanted;
			this.IsScriptWanted = source.IsScriptWanted;
			this.ScriptFileName = source.ScriptFileName;
			this.SpareBool = source.SpareBool;
			this.IsSchemaPreserved = source.IsSchemaPreserved;
			this.IsCopyNameWanted = source.IsCopyNameWanted;
		}

		public override bool Equals(Object target)
		{
			// object is wrong tyoe
			CopyTableModel targetModel = target as CopyTableModel;

			if (!this.SourceDb.Equals(targetModel.SourceDb)) return false;
			if (!this.TargetDb.Equals(targetModel.TargetDb)) return false;
			if (!this.SourceTable.Equals(targetModel.SourceTable)) return false;
			if (!this.TargetTable.Equals(targetModel.TargetTable)) return false;
			if (!this.OtherTables.Equals(targetModel.OtherTables)) return false;
			if (!this.ObjectType.Equals(targetModel.ObjectType)) return false;
			if (this.IsDataWanted != targetModel.IsDataWanted) return false;
			if (this.IsScriptWanted != targetModel.IsScriptWanted) return false;
			if (this.ScriptFileName != targetModel.ScriptFileName) return false;
			if (this.SpareBool != targetModel.SpareBool) return false;
			if (this.IsSchemaPreserved != targetModel.IsSchemaPreserved) return false;
			if (this.IsCopyNameWanted != targetModel.IsCopyNameWanted) return false;
			return true;
		}

		// I think only in cast the object is used in a dictionary
		// Probably not necessary for a model object.
		public override int GetHashCode()
		{
			return this.ToString().GetHashCode();
		}

		// debug only Normally
		public override string ToString()
		{
			return string.Format("{0} {1} {2} {3} | {4} {5} {6} {7}", this.SourceDb, this.TargetDb, this.SourceTable, this.TargetTable,
																		this.IsDataWanted, this.SpareBool, this.IsSchemaPreserved, this.IsCopyNameWanted);
		}

		internal void UpdateTargetTable()
		{
			this.TargetTable = MakeAdjustedTargetTableName(this.SourceTable);
		}

		internal string MakeAdjustedTargetTableName(string objectName)
		{
			// start with source
			String tableName = objectName;
			// may need to nip off the schema if the source has a schema and the table is 
			// being copied to a database without that schema.
			if (!this.IsSchemaPreserved)
			{
			    int pos = objectName.IndexOf(".");
			    if (pos > 0)
			        tableName = tableName.Substring(pos + 1);
			}
			// Respect the copy name adjustment flag
			// ie. when making a copy in the same datanbase the target name must be different
			// So this adds a time stamp. Its a reasonably useful automated way to get 
			// a unique name.
			//if (this.IsCopyNameWanted == true)
			//    // decorate with a datetimestamp
			//    this.TargetTable = MakeCopyName(tableName);
			//else
			//    this.TargetTable = tableName;
			return (this.IsCopyNameWanted == true) ?  MakeCopyName(tableName) : tableName;
		}
		
		public static string MakeCopyName(string rootName)
		{
			return String.Format("{0}_{1}", rootName, MakeDateTimePart());
		}

		private static String MakeDateTimePart()
		{
			System.DateTime now = new DateTime();
			now = DateTime.Now;
			return now.ToString("yyyyMMdd_hhmmss");
		} 
	}

	public class CopyTablePresenter
	{
		// > Boilerplate: references to objects of the pattern
		// keep the original safe until the Save button is pressed
		// compare with the new to set the "dirty" flag 
		public CopyTableModel originalModel;
		public CopyTableModel newModel;

		// friendly name for the model for the view to access
		public CopyTableModel Model
		{
			get { return newModel; }
		}
		// reference the view as there are messages to pass from one to the other
		public CopyTableForm View { get; set; }
		public DbViewDriver.IConnectionConfig ConnectionConfig {get; set;}
		public DbView.ConnectionInfo trueCurrentConnectionInfo;

		public string SourceConnectionString {get; set;}
		public string TargetConnectionString {get; set;}

		public bool IsCopyErr {get; set;}
		public string LastError {get; set;}

		// Constructor
		//
		public CopyTablePresenter(CopyTableModel modelIn, string sourceTable, DbViewDriver.IConnectionConfig connectionConfig)
		{
			// > Boilerplate: Constructor work
			// hold a reference to the original model. 
			this.originalModel = modelIn;
			// create a working copy of the original
			this.newModel = new CopyTableModel();
			this.newModel.CopyFrom(this.originalModel);
			// < Boilerplate: Constructor work
			
			// specialised construction
			// hold the connection config. Used for selecting new target later
			this.ConnectionConfig = connectionConfig;
			// initialise source and target with current connection
			DbView.ConnectionInfo ci = this.ConnectionConfig.GetCurrentConnection();
			this.Model.SourceDb = ci.Name;
			this.Model.SourceTable = sourceTable;
			// Contentious decision to clear the other tables selected regardless
			// of circumstances. Most scenarios are better with this. Unfortunately
			// for the odd one where it isn't better, the user will have to re-enter the list
			// If this is ever encountered in the wild then I'll keep the selections and add a clear button
			this.Model.OtherTables = "";
			// Use retained value unless empty
			if (string.IsNullOrEmpty(this.Model.TargetDb))
				this.Model.TargetDb = ci.Name;
			// hold for replacement (This is to work around an API problem using the connection dialog)
			this.trueCurrentConnectionInfo = ci;

			// check whether there is a target table
			if (string.IsNullOrEmpty(this.Model.TargetTable))
				this.Model.TargetTable = this.Model.SourceTable;

			// Schema check box initialisation
			if (this.Model.SourceTable.IndexOf(".") == -1) 
				this.Model.IsSchemaPreserved = false;
			else
				this.Model.IsSchemaPreserved = (this.Model.TargetTable.IndexOf(".") > 0);

			this.Model.UpdateTargetTable();
		}

		public void OnClose(bool saveModel)
		{
			// restore the current connection information
			this.ConnectionConfig.SetCurrentConnection(this.trueCurrentConnectionInfo);
			// save model 
			if (saveModel)
				this.originalModel.CopyFrom(this.newModel);
		}

		public bool SourceMatchesDestination()
		{
			bool sameDb = (this.Model.SourceDb == this.Model.TargetDb);
			bool sameTable = (this.Model.SourceTable == this.Model.TargetTable);
			return (sameDb && sameTable);
		}

		// Modify the destination name to copy a table to the same database as the source
		// eg MYTABLE to MYTABLE_2008_12_25_140503
		// plus now deal with schema
		public void ModifyTargetObjectName()
		{
			this.Model.UpdateTargetTable();
			// do bindings reflect this?
		}

		public void UserRequestedSelectTargetDatabase()
		{
			// need to save the current connection to work around an API problem.
			DbView.ConnectionInfo pushedCi = this.ConnectionConfig.GetCurrentConnection();
			DbView.ConnectionInfo ci = this.View.SelectTargetDatabase(this.ConnectionConfig);
			if (ci != null)
			{
				this.Model.TargetDb = ci.Name;
			}
			this.ConnectionConfig.SetCurrentConnection(pushedCi);
		}

		public void MemoiseOtherTables(string [] tableNames)
		{
			this.Model.OtherTables = string.Join("|", tableNames);
		}

		public string[] UnMemoiseOtherTables()
		{
			if (string.IsNullOrEmpty(this.Model.OtherTables))
				return new string[] {};
			return this.Model.OtherTables.Split('|');
		}

		///////////////////////////////////////////////////////////////////////////////////////////
		// The actual copy functionality

		public void InitialiseCopyRun()
		{
			InitialiseConnectionInfo();

			// check now 
			this.IsCopyErr = false; // clear error flag

			// if schema is wanted check that it exists on the target
			bool schemaCheckResult = (this.Model.IsSchemaPreserved) ? CheckSchemaExistsOnTarget(this.TargetConnectionString,  this.Model.TargetDb, this.Model.TargetTable) : true;
			if (!schemaCheckResult)
				this.IsCopyErr = true;

			// needs objectifying
			// not an error condition
			//bool targetObjectExists = this.CheckObjectExistsOnTarget(this.TargetConnectionString,  this.Model.TargetDb, this.Model.TargetTable, this.Model.ObjectType);
			//if (targetObjectExists)
			//    this.IsCopyErr = true;
		}

		public void InitialiseConnectionInfo()
		{
			// resolve source and target connections
			this.SourceConnectionString = "";
			this.TargetConnectionString = "";
			List<DbView.ConnectionInfo> connections = this.ConnectionConfig.GetConnections();
			foreach (DbView.ConnectionInfo ci in connections)
			{
				if (ci.Name == this.Model.SourceDb)
					this.SourceConnectionString = SqlServerDataSource.MakeConnectionString(ci);
				if (ci.Name == this.Model.TargetDb)
				{
					this.TargetConnectionString = SqlServerDataSource.MakeConnectionString(ci);
					// tgtDb = ci.Server+"."+ci.Database; // why?
				}
			}
		}

		private string GetConnectionString(string connectionName)
		{
			List<DbView.ConnectionInfo> connections = this.ConnectionConfig.GetConnections();
			foreach (DbView.ConnectionInfo ci in connections)
			{
			    if (ci.Name == connectionName) 
					return SqlServerDataSource.MakeConnectionString(ci);
			}
			return "";
		}

		private bool CheckSchemaExistsOnTarget(string connTarget, String database, String targetTable)
		{
            // allow for schema 
            String[] tableParts = Helper.ParseTableName(targetTable);
            if (tableParts.GetLength(0) <= 1) return true; // not necessary 
            // name must be schemaa'd or else.
            String sql = String.Format("if exists(select * from sys.schemas where name = '{0}') select 'Y' else select 'N'", tableParts[1]);
            DataSet ds;
            Helper.GenericMakeDset(connTarget, sql, "CHECK", out ds);
            if (ds.Tables[0].Rows[0][0].ToString() == "N") // !Exists!
            {
                this.LastError = String.Format(
@"Schema [ {0} ] doesn't exist on target database
{1}

Either uncheck the Keep schema box to copy the table to 
the dbo owner or abort the copy and create the schema
[ {0} ] on the target database", tableParts[1], database);
                return false;
            }
			return true;
		}
		
		public bool CheckObjectExistsOnTarget()
		{
			return this.CheckObjectExistsOnTarget(this.TargetConnectionString, this.Model.TargetDb, this.Model.TargetTable, this.Model.ObjectType);
		}

		// temp restriction: cannot copy if target exists
		private bool CheckObjectExistsOnTarget(string connTarget, String database, string targetTable, CopyTableModel.CopyObjectType objectType)
		{
			// add object type to sql to avoids embarassment.
			string objectTypeCode = GetObjectTypeCode(objectType);

			String sql = string.Format("select count(1) from sys.objects where name = '{0}' and type = '{1}'", this.Model.TargetTable, objectTypeCode);
			DataSet ds;
			Helper.GenericMakeDset(connTarget, sql, "CHECK", out ds);
			if (ds.Tables[0].Rows[0][0].ToString() == "1") // !Exists!
			{
				// temp: error stuff - not needed now I think
//                this.LastError = String.Format(
//@"[ {0} ] exists on the target database
//{1}
//
//This version does not permit a copy to overwrite an existing 
//object. Please check the use copy name option or cancel the copy", targetTable, database);
				return true;
			}
			return false;
		}

		private string GetObjectTypeCode(CopyTableModel.CopyObjectType objectType)
		{
			return (objectType == CopyTableModel.CopyObjectType.Table) ? "U" :
					(objectType == CopyTableModel.CopyObjectType.View) ? "V" :
					(objectType == CopyTableModel.CopyObjectType.Procedure) ? "P" : "?";
		}
		
		internal void CreateTargetTable()
		{
			try
			{
				SqlTableSchema schema = Helper.MakeSchemaFor(this.Model.SourceTable, this.SourceConnectionString);
				SqlServerScripter scripter = new SqlServerScripter(this.Model.SourceTable, schema);
				scripter.CopyTable(this.SourceConnectionString, this.TargetConnectionString, 
									this.Model.SourceTable, this.Model.TargetTable, false, null);
			}
			catch (Exception exc)
			{
				this.IsCopyErr = true;
				this.LastError = exc.Message;
			}
		}
		
		internal void CreateTargetObject(bool objectExists)
		{
			try
			{
				// get source
				string script = Helper.GetSourceCode(this.SourceConnectionString, this.Model.SourceTable);
				
				// rename if necessary
				if (this.Model.SourceTable != this.Model.TargetTable)
				{
					string seek = this.Model.SourceTable.ToUpper();
					int ipos = script.ToUpper().IndexOf(seek);
					// let it error
					script = script.Substring(0, ipos) + this.Model.TargetTable + script.Substring(ipos + this.Model.SourceTable.Length);
				}
				
				// if exists swap from create x to alter x
				string objectTypeText = this.Model.ObjectType.ToString();
				if (objectExists)
				{
					script = this.ChangeCreateToAlter(script, objectTypeText);
				}
				
				// and execute
				bool success;
				string result = Helper.GenericExecute(this.TargetConnectionString, script, out success);
				if (!success)
				{
					this.IsCopyErr = true;
					this.LastError = MakeObjectCopyErrorMessage(result);
					
				}
			}
			catch (Exception exc)
			{
				this.IsCopyErr = true;
				this.LastError = exc.Message;
			}
		}

		private string CreateScript(string connectionString, string sourceName, string targetName, CopyTableModel.CopyObjectType objectType, bool objectExists)
		{
			string script = Helper.GetSourceCode(connectionString, sourceName);
			// rename if necessary
			if (sourceName != targetName)
			{
				string seek = sourceName.ToUpper();
				int ipos = script.ToUpper().IndexOf(seek);
				// let it error
				script = script.Substring(0, ipos) + targetName + script.Substring(ipos + sourceName.Length);
			}
			// if exists swap from create x to alter x
			string objectTypeText = this.Model.ObjectType.ToString();
			if (objectExists)
			{
				script = this.ChangeCreateToAlter(script, objectTypeText);
			}
			return script;
		}

		private string ChangeCreateToAlter(string script, string objectTypeText)
		{
			int ipos = FindAlterInsertionPoint(script, objectTypeText);
			// cater for "create procedure" and "create proc"
			if (ipos == -1)
				ipos = FindAlterInsertionPoint(script, "PROC");
			// let it error
			script = script.Substring(0, ipos) + "alter" + script.Substring(ipos + 6);
			return script;
		}

		private int FindAlterInsertionPoint(string script, string objectTypeText)
		{
			string seek = "CREATE " + objectTypeText.ToUpper();
			int ipos = script.ToUpper().IndexOf(seek);
			return ipos;
		}

		private string MakeObjectCopyErrorMessage(string result)
		{
			string invalidObjectMessage = @"
The copy was not successful most likely because the object references
an object not present in the target database. You will need to create 
or copy that object before copying this one. Below is the error reported
by SQL Server:

{0}
";
			string noPermissionMessage = @"
The copy was not successful most likely because the connection to
the target database does not have permission to create or update 
objects. Below is the error reported by SQL Server:

{0}
";
			// check message for invalid object and pad if necessary
			if (result.Contains("Invalid object"))
			{
				return string.Format(invalidObjectMessage, result);
			}
			if (result.Contains("permission"))
			{
				return string.Format(noPermissionMessage, result);
			}
			return result;
		}

		public void RunBackGroundTableUpdate()
		{
			// at this point we should have two tables in the target database
			// this.Model.SourceTable has the right name but the wrong data
			// this.Model.TargetTable has the right data but the wrong name
			// so transfer data from target to source and drop the target

			// 1st non-threaded, but aiming to be threaded.

			// Get the fields of the update table
			SqlTableSchema schema = Helper.MakeSchemaFor(this.Model.SourceTable, this.TargetConnectionString);
			// a SqlScriptingHelper generates fragments used for scripting updates, schemas etc. 
			SqlScriptingHelper scriptHelper = new SqlScriptingHelper(this.Model.TargetTable, schema);
			// a SqlServerScripter assembles fragments into a usable script
			SqlServerScripter scripter = new SqlServerScripter(this.Model.TargetTable, schema);

			// actually what this thinks is the source is actually the target and vv.
			string updateSource = this.Model.TargetTable;
			string updateTarget = this.Model.SourceTable;
			String script = scripter.ScriptContentCopy(updateSource, updateTarget, scriptHelper);
			script += "\n ;drop table " + updateSource;

			ContentRefreshThreadClass worker = new ContentRefreshThreadClass(null, this.TargetConnectionString, script);
			worker.KickOffThread();
		}

		// user wants to copy multiple tables. Use source details to get the data
		// Remember schema
		public string [] GetAvailableObjectNames(CopyTableModel.CopyObjectType objectType)
		{
			string sourceConnectionString = GetConnectionString(this.Model.SourceDb);
			string sqlTemplate = @"
select Name, isDbo
 from ( select case when sc.name='dbo' then o.name 
		 			else sc.name+'.'+o.name end as Name, 
				case when sc.name='dbo' then 0 else 1 end as isDbo
			 from sys.objects o
		left join sys.schemas sc on sc.schema_id = o.schema_id
			where type = ('{1}') 
		) q
   where Name != '{0}'
  order by isDbo, Name";
			
			string objectTypeCode = this.GetObjectTypeCode(objectType);
			string sql = string.Format(sqlTemplate, this.Model.SourceTable, objectTypeCode); // exclude primary selected table.
			
			DataTable dt = Helper.GenericQuery(sourceConnectionString, sql);
			string [] tables = new string[dt.Rows.Count];
			int idx = 0;
			foreach (DataRow dr in dt.Rows)
			{
				tables[idx++] = dr[0].ToString();
			}
			return tables;
		}

		/////////////////////////////////////////////////////////////////////////////////////////////////
		// scripting

		internal string ScriptObject(string dbobject)
		{
			
			string oneScript = (this.Model.ObjectType == CopyTableModel.CopyObjectType.Table) ? scriptTable(dbobject)
								: (this.Model.ObjectType == CopyTableModel.CopyObjectType.View) ? scriptView(dbobject)
								: (this.Model.ObjectType == CopyTableModel.CopyObjectType.Procedure) ? scriptProcedure(dbobject)
								: "";
			return oneScript;
		}

		private string scriptProcedure(string dbobject)
		{
			string objectType = "PROCEDURE";
			string objectTypeCode = this.GetObjectTypeCode(CopyTableModel.CopyObjectType.Procedure);
			string tgtObject = this.Model.MakeAdjustedTargetTableName(dbobject);//(this.Model.IsCopyNameWanted) ? CopyTableModel.MakeCopyName(dbobject) : dbobject;
			bool objectExists = true; // it will exist becuase the script will create it if it does not alerady.
			//
			string script = this.CreateScript(this.SourceConnectionString, dbobject, tgtObject, this.Model.ObjectType, objectExists);
			return this.ScriptExists(tgtObject, objectType, objectTypeCode) + script;
		}

		private string scriptView(string dbobject)
		{
			string objectType = "VIEW";
			string objectTypeCode = this.GetObjectTypeCode(CopyTableModel.CopyObjectType.View);
			string tgtObject = this.Model.MakeAdjustedTargetTableName(dbobject);
			bool objectExists = true; // it will exist becuase the script will create it if it does not alerady.
			//
			string script = this.CreateScript(this.SourceConnectionString, dbobject, tgtObject, this.Model.ObjectType, objectExists);
			return this.ScriptExists(tgtObject, objectType, objectTypeCode) + script;
		}

		private string scriptTable(string dbobject)
		{
			string objectType = "TABLE";
			string objectTypeCode = this.GetObjectTypeCode(CopyTableModel.CopyObjectType.Table);
			SqlTableSchema schema = Helper.MakeSchemaFor(dbobject, this.SourceConnectionString);
			string tgtObject = this.Model.MakeAdjustedTargetTableName(dbobject);
			SqlServerScripter newTableBuilder = new SqlServerScripter(tgtObject, schema);
			String script = newTableBuilder.ScriptCreateTable();
			
			string[] parts = Helper.ParseTableName(tgtObject);  // parts[0] is always the name parts[1] is the schema if present.
			string schemaTest = MakeSchemaTest(parts);
			string template = @"{5}
if exists(select 1 from sys.objects where (name = '{4}' and type = '{2}') and object_id = object_id('{0}'))
	print 'Table exists {0}. No action taken'
else
{3}
GO
";		
			return string.Format(template, tgtObject, objectType, objectTypeCode, script, parts[0], schemaTest);
		}

		private string ScriptExists(string dbobject, string objectType, string objectTypeCode)
		{
			string template1 = (objectTypeCode == "U") ? "drop table {0}" : 
								(objectTypeCode == "V") ? "create {1} {0} as select 1 as a" : 
								(objectTypeCode == "P") ? "create {1} {0} as" : "";

			// deal with schema in existance check
			// you cannot find schema.objectname in the name column of sys.objects
			// instead match on name and type to ensure the object is the right type of object
			// then match the object id from that on the result of the OBJECT_ID() funtion
			// with the schema.objectname as the parameter.
			string[] parts = Helper.ParseTableName(dbobject);  // parts[0] is always the name parts[1] is the schema if present.
			string schemaTest = MakeSchemaTest(parts);
			
			string template = @"{5}
if not exists(select 1 from sys.objects where (name = '{4}' and type = '{2}') and object_id = object_id('{0}'))
	exec('{3}')
GO
";			
			string sql = string.Format(template1, Helper.SafeTableName(dbobject), objectType);
			return string.Format(template, dbobject, objectType, objectTypeCode, sql, parts[0], schemaTest);
		}

		private static string MakeSchemaTest(string[] parts)
		{
			string schemaTest = "";								// empty is good for no schema
			if (parts.Length > 1) // ie schema
			{
				string makeSchemaTemplate = @"
if not exists(select 1 from sys.schemas where name = '{0}') exec('create schema {0}')
GO";
				schemaTest = string.Format(makeSchemaTemplate, parts[1]);
			}
			return schemaTest;
		}
	}
}
